package dao;

import pojo.Operator;
import utils.JDBCUtils;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class OperatorDAOImpl {

    private static final String TABLE_NAME = "Operator";

    public static void add(Operator operator) throws SQLException {
        String sql = "INSERT INTO " + TABLE_NAME + " (operator_id, work_area, warehouse_number, id_number, department_id) VALUES (?, ?, ?, ?, ?)";
        JDBCUtils.execute(sql, null, operator.getWorkArea(), operator.getWarehouseNumber(), operator.getIdNumber(), operator.getDepartmentID());
    }

    public static void delete(int operatorID) throws SQLException {
        String sql = "DELETE FROM " + TABLE_NAME + " WHERE operator_id = ?";
        JDBCUtils.execute(sql, operatorID);
    }

    public static void update(Operator operator) throws SQLException {
        String sql = "UPDATE " + TABLE_NAME + " SET work_area = ?, warehouse_number = ?, id_number = ?, department_id = ? WHERE operator_id = ?";
        JDBCUtils.execute(sql, operator.getWorkArea(), operator.getWarehouseNumber(), operator.getIdNumber(), operator.getDepartmentID(), operator.getOperatorID());
    }

    public static Operator getById(int operatorID) throws SQLException {
        String sql = "SELECT * FROM " + TABLE_NAME + " WHERE operator_id = ?";
        ResultSet resultSet = (ResultSet) JDBCUtils.execute(sql, operatorID);
        if (resultSet.next()) {
            return createOperatorFromResultSet(resultSet);
        }
        return null;
    }

    public static List<Operator> getAll() throws SQLException {
        List<Operator> operators = new ArrayList<>();
        String sql = "SELECT * FROM " + TABLE_NAME;
        ResultSet resultSet = (ResultSet) JDBCUtils.execute(sql);
        while (resultSet.next()) {
            Operator operator = createOperatorFromResultSet(resultSet);
            operators.add(operator);
        }
        return operators;
    }

    public static int getCount() throws SQLException {
        String sql = "SELECT COUNT(*) AS count FROM " + TABLE_NAME;
        ResultSet resultSet = (ResultSet) JDBCUtils.execute(sql);
        if (resultSet.next()) {
            return resultSet.getInt("count");
        }
        return 0;
    }

    public static List<Operator> getByPage(int start, int pageSize) throws SQLException {
        List<Operator> operators = new ArrayList<>();
        String sql = "SELECT * FROM " + TABLE_NAME + " LIMIT ?, ?";
        ResultSet resultSet = (ResultSet) JDBCUtils.execute(sql, start, pageSize);
        while (resultSet.next()) {
            Operator operator = createOperatorFromResultSet(resultSet);
            operators.add(operator);
        }
        return operators;
    }

    public static List<Operator> findByKeyword(String keyword) throws SQLException {
        List<Operator> operators = new ArrayList<>();
        String sql = "SELECT * FROM " + TABLE_NAME + " WHERE work_area LIKE ?";
        ResultSet resultSet = (ResultSet) JDBCUtils.execute(sql, "%" + keyword + "%");
        while (resultSet.next()) {
            Operator operator = createOperatorFromResultSet(resultSet);
            operators.add(operator);
        }
        return operators;
    }

    public static List<Operator> findByPageAndKeyword(int start, int pageSize, String keyword) throws SQLException {
        List<Operator> operators = new ArrayList<>();
        String sql = "SELECT * FROM " + TABLE_NAME + " WHERE work_area LIKE ? LIMIT ?, ?";
        ResultSet resultSet = (ResultSet) JDBCUtils.execute(sql, "%" + keyword + "%", start, pageSize);
        while (resultSet.next()) {
            Operator operator = createOperatorFromResultSet(resultSet);
            operators.add(operator);
        }
        return operators;
    }

    private static Operator createOperatorFromResultSet(ResultSet resultSet) throws SQLException {
        int operatorID = resultSet.getInt("operator_id");
        String workArea = resultSet.getString("work_area");
        int warehouseNumber = resultSet.getInt("warehouse_number");
        String idNumber = resultSet.getString("id_number");
        int departmentID = resultSet.getInt("department_id");
        return new Operator(operatorID, workArea, warehouseNumber, idNumber, departmentID);
    }
}
